/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.sadhar.sipp.laporananggaranrabrak;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.sadhar.sia.common.ClassConnection;

/**
 *
 * @author kris
 */
public class LaporanAnggaranRabRkaDAOImpl implements LaporanAnggaranRabRkaDAO {

    public LaporanAnggaranRabRkaDAOImpl() {
        ClassConnection.getTransactionProxyFactoryBean().setTarget(this);
    }

    public List<Map> getAllByTahunAndKode(String kode, String tahun, String tahunKelompok) {
        String sql = "SELECT jenisBiaya,namaPosBiaya,SUM(harga*kuantitas)  as total FROM ppmk.anggaranRABNonRapatKerja AS rab" +
                " INNER JOIN ppmk.kelompokBiaya AS klpk ON rab.kodePosBiaya=klpk.kodePosBiaya" +
                " INNER JOIN ppmk.posBiaya AS pos ON pos.kodePosBiaya=rab.kodePosBiaya" +
                " INNER JOIN ppmk.jenisBiaya AS jns ON jns.kodeJenisBiaya=klpk.kodeJenisBiaya" +
                " WHERE isRevisi='Y' AND isSudahDiajukan='Y'" +
                " AND kodeUnit=? " +
                " AND rab.tahun=? " +
                " AND klpk.tahunKelompok=? GROUP BY rab.kodePosBiaya" +
                " UNION ALL" +
                " SELECT jenisBiaya,namaPosBiaya,IF(jumlahHari=0,SUM(harga*kuantitas),SUM(harga*kuantitas*jumlahHari)) " +
                " FROM ppmk.kelompokBiaya AS klpk" +
                " INNER JOIN ppmk.subkelompokAnggaranBiaya AS sub ON klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya" +
                " INNER JOIN ppmk.anggaranBiayaRapatKerjaDanKegiatan AS rab_rapat ON rab_rapat.kodePosBiaya=sub.KodePosBiaya" +
                " INNER JOIN ppmk.rapatKerjaUnit AS rapat ON rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit" +
                " INNER JOIN ppmk.posBiaya AS pos ON pos.kodePosBiaya=klpk.kodePosBiaya" +
                " INNER JOIN ppmk.jenisBiaya AS jns ON jns.kodeJenisBiaya=klpk.kodeJenisBiaya" +
                " WHERE klpk.kodeJenisBiaya=1 AND klpk.kodePosBiaya=5" +
                " AND tahunKelompok=? " +
                " AND rapat.tahun=? " +
                " AND isRapat='Y'" +
                " AND rapat.kodeUnit=? " +
                " AND isRevisi='Y'" +
                " AND isSudahDiajukan='Y' GROUP BY klpk.kodePosBiaya" +
                " UNION ALL" +
                " SELECT jenisBiaya,namaPosBiaya,IF(jumlahHari=0,SUM(harga*kuantitas),SUM(harga*kuantitas*jumlahHari))" +
                " FROM ppmk.kelompokBiaya AS klpk" +
                " INNER JOIN ppmk.subkelompokAnggaranBiaya AS sub ON klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya" +
                " INNER JOIN ppmk.anggaranBiayaRapatKerjaDanKegiatan AS rab_rapat ON rab_rapat.kodePosBiaya=sub.KodePosBiaya" +
                " INNER JOIN ppmk.kegiatanUnit AS keg ON rab_rapat.kodeRapatKerjaSubKegiatan=keg.kodeKegiatanUnit" +
                " INNER JOIN ppmk.posBiaya AS pos ON pos.kodePosBiaya=klpk.kodePosBiaya" +
                " INNER JOIN ppmk.jenisBiaya AS jns ON jns.kodeJenisBiaya=klpk.kodeJenisBiaya" +
                " INNER JOIN ppmk.kelompokRKA AS klpkRKA ON klpkRKA.kodeKelompokKegiatan=keg.kodeKelompokKegiatan" +
                " AND klpkRKA.kodePosBiaya=klpk.kodePosBiaya" +
                " WHERE klpk.kodeJenisBiaya=2" +
                " AND tahunKelompok=? " +
                " AND isRapat='N'" +
                " AND keg.tahun=?" +
                " AND keg.kodeUnit=?" +
                " AND isRevisi='Y'" +
                " AND isSudahDiajukan='Y' GROUP BY  klpk.kodePosBiaya";


        List<Map> maps = ClassConnection.getJdbc().queryForList(sql, new Object[]{
                    kode, tahun, tahunKelompok,
                    tahunKelompok, tahun, kode,
                    tahunKelompok, tahun, kode
                });
        return maps;
    }

    public Map getUnitKerjaByKodeUnit(String kode) {
        String sql = "select Nama_unit_kerja from kamus.unkerja where Kd_unit_kerja=? ";
        List<Map> result = ClassConnection.getJdbc().queryForList(sql, new Object[]{kode});
        Map m = new HashMap();
        for (Map map : result) {
            m = map;
        }
        return m;
    }

    public Map getTotalRkaRab(String kode, String tahun) {
        String sql = "SELECT SUM(pf1.totalPlafon) as rka," +
                " SUM(pf2.totalPlafon) rab," +
                " SUM(pf1.totalPlafon) + SUM(pf2.totalPlafon) totalPlafon " +
                " FROM ppmk.plafon pf1, ppmk.plafon pf2" +
                " WHERE  pf1.kodeunit = ? " +
                " AND pf1.kodeJenisAnggaran = 1 " +
                " AND pf2.kodeunit = ? " +
                " AND pf2.kodeJenisAnggaran = 2 " +
                " AND pf1.tahun=? " +
                " AND pf2.tahun=? ";

        List<Map> result = ClassConnection.getJdbc().queryForList(sql, new Object[]{kode, kode, tahun, tahun});
        Map m = new HashMap();
        for (Map map : result) {
            m = map;
        }
        return m;

    }

    public Map getTahunKelompokAnggaranYangDigunakan(String tahun) {
        String sql = "select tahunKelompok from ppmk.kelompokAnggaranYangDigunakan where" +
                " tahunAnggaran=?";
        List<Map> result = ClassConnection.getJdbc().queryForList(sql, new Object[]{tahun});
        Map m = new HashMap();
        for (Map map : result) {
            m = map;
        }
        return m;
    }

    public Map getTotalRAB(String kode, String tahun, String tahunKelompok) {
        String sql = "select sum(jumlah) as totalRAB from (select jenisBiaya,namaPosBiaya,sum(harga*kuantitas)as jumlah from ppmk.anggaranRABNonRapatKerja as rab " +
                " inner join ppmk.kelompokBiaya as klpk on rab.kodePosBiaya=klpk.kodePosBiaya " +
                " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=rab.kodePosBiaya " +
                " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya" +
                " where isRevisi='Y' and isSudahDiajukan='Y' and " +
                " kodeUnit=? and rab.tahun=? and " +
                " klpk.tahunKelompok=? group by rab.kodePosBiaya " +
                " UNION ALL " +
                " select jenisBiaya,namaPosBiaya,if(jumlahHari=0,sum(harga*kuantitas),sum(harga*kuantitas*jumlahHari)) as jumlah from ppmk.kelompokBiaya as klpk" +
                " inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya " +
                " inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on  rab_rapat.kodePosBiaya=sub.KodePosBiaya " +
                " inner join ppmk.rapatKerjaUnit as rapat on rab_rapat.kodeRapatKerjaSubKegiatan=rapat.idRapatKerjaUnit " +
                " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya " +
                " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya=klpk.kodeJenisBiaya where klpk.kodeJenisBiaya=1" +
                "  and klpk.kodePosBiaya=5 and isRapat='Y' and tahunKelompok=? and rapat.tahun=? " +
                "  and rapat.kodeUnit=? and isRevisi='Y' and isSudahDiajukan='Y' group by klpk.kodePosBiaya) as sub";

        List<Map> result = ClassConnection.getJdbc().queryForList(sql, new Object[]{kode, tahun, tahunKelompok, tahunKelompok, tahun, kode});
        Map m = new HashMap();
        for (Map map : result) {
            m = map;
        }
        return m;

    }

    public Map getTotalRKA(String kode, String tahun, String tahunKelompok) {
        String sql = "select sum(jumlah) as totalRKA from (select jenisBiaya,namaPosBiaya,if(jumlahHari=0,sum(harga*kuantitas),sum(harga*kuantitas*jumlahHari))as jumlah from ppmk.kelompokBiaya as klpk " +
                " inner join ppmk.subkelompokAnggaranBiaya as sub on klpk.kodeKelompokBiaya=sub.kodeKelompokBiaya " +
                " inner join ppmk.anggaranBiayaRapatKerjaDanKegiatan as rab_rapat on rab_rapat.kodePosBiaya=sub.KodePosBiaya " +
                " inner join ppmk.kegiatanUnit as keg on rab_rapat.kodeRapatKerjaSubKegiatan=keg.kodeKegiatanUnit " +
                " inner join ppmk.posBiaya as pos on pos.kodePosBiaya=klpk.kodePosBiaya " +
                " inner join ppmk.jenisBiaya as jns on jns.kodeJenisBiaya= klpk.kodeJenisBiaya " +
                " inner join ppmk.kelompokRKA as klpkRKA on klpkRKA.kodeKelompokKegiatan=keg.kodeKelompokKegiatan " +
                " and klpkRKA.kodePosBiaya=klpk.kodePosBiaya where klpk.kodeJenisBiaya=2 " +
                " and tahunKelompok=? and isRapat='N' and keg.tahun=? and keg.kodeUnit=? " +
                " and isRevisi='Y' and isSudahDiajukan='Y' group by klpk.kodePosBiaya) as sub";

        List<Map> result = ClassConnection.getJdbc().queryForList(sql, new Object[]{tahunKelompok, tahun, kode});
        Map m = new HashMap();
        for (Map map : result) {
            m = map;
        }
        return m;

    }
}
